<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Evaluacion_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    /**
     * Verifica que el usuario y el password coincidan
     * @param string $usuario Nombre de usuario
     * @param string $pass Password
     * @return bool false si el loggin fue incorrecto true si fue correcto
     * *
     */
    function verificar_datos($usuario, $pass) {
        $query = "  select a_administrativo_id,
                            concat( a_nombre,' ',a_apellido) as a_nombre,
                            a_email,
                            ta.ta_tipo_administrativo_id,
                            ta.ta_tipo,
                            a_imagen
                    from administrativo a join tipo_administrativo ta 
                    on(ta.ta_tipo_administrativo_id=a.ta_tipo_administrativo_id)
                    where a.a_usuario=? and a.a_pass=?";
        $resultado = $this->db->query($query, array($usuario, $pass));
        $out = $resultado->row();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * 
     * @param type $ta_tipo_aspi_id
     * @return type
     * *
     */
    function questionList($ta_tipo_aspi_id) {
        $query = "  select p_pregunta_id 
                    from pregunta p 
                    join subcategoria sc on(p.s_subcategoria_id=sc.s_subcategoria_id)
                    join categoria c on(c.c_categoria_id=sc.c_categoria_id)
                    join tipo_aspi ta on(ta.ta_tipo_aspi_id=c.ta_tipo_aspi_id)
                    where ta.ta_tipo_aspi_id=?";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id));
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * Retorna el nombre de las sucursales
     * @return array Nombre de las sucursales
     * *
     */
    function lista_sucursales() {
        $query = 'select s_sucursal_id,s_descripcion as s_nombre from sucursal order by s_nombre asc';
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * Para comenzar con la evaluación primero se crea un cuestionario que <br>
     * generará el id que se utilizará para guardar el resto de las preguntas<br>
     * @param date $c_fecha fecha de la auditoria
     * @param time $c_horario_inicio hora de inicio de la auditoria
     * @param int $c_comensales numero de comenzales en el restaurante
     * @param int $c_aforo aforo total del restaurante
     * @param int $s_sucursal_id id de la sucursal evaluada
     * @return int id del cuestionario actual
     * *
     */
    function crear_cuestionario($c_fecha, $c_hora_inicio, $c_comensales, $c_aforo, $s_sucursal_id, $c_fecha_usuario, $c_ip_inicia_cuestionario) {
        $insert_query = "insert into cuestionario(  c_fecha,
                                                    c_hora_inicio,
                                                    c_comensales,
                                                    c_aforo,
                                                    s_sucursal_id,
                                                    c_fecha_usuario,
                                                    c_ip_inicia_cuestionario)
                 values(?,?,?,?,?,?,?)";
        $this->db->query($insert_query, array($c_fecha, $c_hora_inicio, $c_comensales, $c_aforo, $s_sucursal_id, $c_fecha_usuario, $c_ip_inicia_cuestionario));
        $query = "select max(c_cuestionario_id) as id from cuestionario";
        $resultado = $this->db->query($query);
        $out = $resultado->row();
        return $out;
    }

    /**
     * Retorna el nombre de la sucursal a la que pertenece el cuestionario
     * @param int $c_cuestionario_id
     * @return string Nombre de la sucursal
     * *
     */
    public function datos_sucursal($c_cuestionario_id) {
        $query = "select s_descripcion as s_nombre from sucursal s join cuestionario c on(s.s_sucursal_id=c.s_sucursal_id) where c.c_cuestionario_id=$c_cuestionario_id";
        $resultado = $this->db->query($query);
        $out = $resultado->row();
        return $out;
    }

    /**
     * Guarda la repuesta de una pregunta , aplica para todos los tipos de ASPI
     * @param int $c_cuestionario_id id del cuestionario actual
     * @param int $p_pregunta_id id de la pregunta actual
     * @param int $on_opcion_na_id puntos obtenidos para la pregunta
     * *
     */
    public function guardar_respuesta($c_cuestionario_id, $p_pregunta_id, $on_opcion_na_id) {
        $query = "  insert into calificacion(p_pregunta_id,c_cuestionario_id,on_opcion_na_id)
                    values($p_pregunta_id,$c_cuestionario_id,$on_opcion_na_id)";
        $this->db->query($query);
    }

    /* retorna la lista de los formularios ASPI
     * 
     * @return array lista de tipos ASPI
     * *
     */

    public function tipos_aspi() {
        $query = "SELECT ta_tipo_aspi_id,ta_tipo FROM tipo_aspi";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    # metodos para reporte
    /** Trae un bloque de respuestas de la categoria  y subcategoria<br>
     * especificadas, se utiliza para generar los reportes con el formato<br>
     * de los formularios ASPI 
     * @param int $ta_tipo_aspi_id id del tipo de ASPI
     * @param int $c_cuestionario_id id del cuestionario que se va a buscar
     * @param char $c_categoria tipo de categoria de A|S|P|I
     * @param char $s_subcategoria tipo de subcategoria A|B|C|D...
     * @return array lista de resultados
     * *
     */

    public function resp_xsubcat($ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria, $s_subcategoria) {
        $query = "  select 	concat(c.c_categoria,'-',p.p_matricula) as sclave, 
                                p.p_pregunta, 
                                p.p_pp, 
                                p.p_pc,
                                opn.on_opcion,
                                opn.on_opcion_na_id,
                                sc.s_subcategoria, 
                                c.c_categoria, 
                                p.p_pregunta_id, 
                                cal.c_calificacion_id 
                    FROM pregunta p 
                    join subcategoria sc on(p.s_subcategoria_id=sc.s_subcategoria_id) 
                    join categoria c on(c.c_categoria_id=sc.c_categoria_id) 
                    join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id) 
                    join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id) 
                    join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id) 
                    join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                    
                    where ta.ta_tipo_aspi_id=? 
                    and cu.c_cuestionario_id=? 
                    and c.c_categoria=? 
                    and sc.s_subcategoria=?
                    order by p.p_pregunta_id asc";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria, $s_subcategoria));
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * 
     * @param type $ta_tipo_aspi_id
     * @param type $c_cuestionario_id
     * @return type
     * 
     * Utima Modificacion: 6/5/2015
     * Version 0.2
     * Modificacion en calific, ya que se sumaban los valores incorrectos
     */
    public function totals($ta_tipo_aspi_id, $c_cuestionario_id) {
        $query = "  select  sum(x.puntos_obtenidos) as puntos_obtenidos,
                            sum(x.criticos) as criticos,
                            sum(x.desviaciones) as desviaciones,
                            sum(x.puntos_posibles) as puntos_posibles,
                            round((sum(x.puntos_obtenidos)/sum(x.puntos_posibles))*100) as calific
                    from(
                    select  if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,(b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0))) as puntos_obtenidos,    
                            if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,round((( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )/b.puntos_posibles)*100)) as calific,    
                            b.criticos, 
                            b.desviaciones,
                            b.puntos_posibles

                        from (          
                                    select      sum(case  when opn.on_opcion in('DESVIACION','EMPTY') then p.p_pp else 0 end) as pdesviaciones, 
                                                sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos,
                                                sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones,
                                                sum(p.p_pp) as puntos_posibles,
                                                (   select sum(x.puntos_posibles) as pcriticos 
                                                        from (  select      sc.s_subcategoria,
                                                                            sum(p.p_pp) as puntos_posibles
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                 and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria='A'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                        ) as x  
                                                ) as pcriticos,
                                                (   select  sum(hk.frederick) as c
                                                      from (    
                                                                select     sc.s_subcategoria,
                                                                        sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as frederick
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                 and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria='A'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                       ) as hk  
                                                ) as comp_desviacion

                                        FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                        join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                        join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                        join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                        join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                        join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                        where opn.on_opcion!='NO APLICA' 
                                         and ta.ta_tipo_aspi_id=?    
                                        and cu.c_cuestionario_id=?    
                                        and c.c_categoria='A'

                            ) as b
                    union all
                    select  if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,(b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0))) as puntos_obtenidos,    
                            if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,round((( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )/b.puntos_posibles)*100)) as calific,    
                            b.criticos, 
                            b.desviaciones,
                            b.puntos_posibles

                        from (          
                                    select      sum(case  when opn.on_opcion in('DESVIACION','EMPTY') then p.p_pp else 0 end) as pdesviaciones, 
                                                sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos,
                                                sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones,
                                                sum(p.p_pp) as puntos_posibles,
                                                (   select sum(x.puntos_posibles) as pcriticos 
                                                        from (  select      sc.s_subcategoria,
                                                                            sum(p.p_pp) as puntos_posibles
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                 and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?   
                                                                and c.c_categoria='S'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                        ) as x  
                                                ) as pcriticos,
                                                (   select  sum(hk.frederick) as c
                                                      from (    
                                                                select     sc.s_subcategoria,
                                                                        sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as frederick
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria='S'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                       ) as hk  
                                                ) as comp_desviacion

                                        FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                        join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                        join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                        join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                        join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                        join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                        where opn.on_opcion!='NO APLICA' 
                                        and ta.ta_tipo_aspi_id=?    
                                        and cu.c_cuestionario_id=?    
                                        and c.c_categoria='S'

                            ) as b
                    union all        
                    select  if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,(b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0))) as puntos_obtenidos,    
                            if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,round((( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )/b.puntos_posibles)*100)) as calific,    
                            b.criticos, 
                            b.desviaciones,
                            b.puntos_posibles

                        from (          
                                    select      sum(case  when opn.on_opcion in('DESVIACION','EMPTY') then p.p_pp else 0 end) as pdesviaciones, 
                                                sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos,
                                                sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones,
                                                sum(p.p_pp) as puntos_posibles,
                                                (   select sum(x.puntos_posibles) as pcriticos 
                                                        from (  select      sc.s_subcategoria,
                                                                            sum(p.p_pp) as puntos_posibles
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria='P'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                        ) as x  
                                                ) as pcriticos,
                                                (   select  sum(hk.frederick) as c
                                                      from (    
                                                                select     sc.s_subcategoria,
                                                                        sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as frederick
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?   
                                                                and c.c_categoria='P'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                       ) as hk  
                                                ) as comp_desviacion

                                        FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                        join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                        join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                        join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                        join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                        join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                        where opn.on_opcion!='NO APLICA' 
                                        and ta.ta_tipo_aspi_id=?    
                                        and cu.c_cuestionario_id=?    
                                        and c.c_categoria='P'

                            ) as b
                    union all
                    select  if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,(b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0))) as puntos_obtenidos,    
                            if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,round((( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )/b.puntos_posibles)*100)) as calific,    
                            b.criticos, 
                            b.desviaciones,
                            b.puntos_posibles

                        from (          
                                    select      sum(case  when opn.on_opcion in('DESVIACION','EMPTY') then p.p_pp else 0 end) as pdesviaciones, 
                                                sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos,
                                                sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones,
                                                sum(p.p_pp) as puntos_posibles,
                                                (   select sum(x.puntos_posibles) as pcriticos 
                                                        from (  select      sc.s_subcategoria,
                                                                            sum(p.p_pp) as puntos_posibles
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria='I'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                        ) as x  
                                                ) as pcriticos,
                                                (   select  sum(hk.frederick) as c
                                                      from (    
                                                                select     sc.s_subcategoria,
                                                                        sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as frederick
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria='I'

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                       ) as hk  
                                                ) as comp_desviacion

                                        FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                        join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                        join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                        join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                        join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                        join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                        where opn.on_opcion!='NO APLICA' 
                                        and ta.ta_tipo_aspi_id=?    
                                        and cu.c_cuestionario_id=?    
                                        and c.c_categoria='I'

                            ) as b
                    ) as x  ";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id,$ta_tipo_aspi_id, $c_cuestionario_id));
        $out = $resultado->row();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * 
     * @param type $ta_tipo_aspi_id
     * @param type $c_cuestionario_id
     * @param type $c_categoria
     * @return type
     * *
     */
        public function totalXCat($ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria) {
        $query = "  select  if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,(b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0))) as puntos_obtenidos,    
                            if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,round((( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )/b.puntos_posibles)*100)) as calific,    
                            b.criticos, 
                            b.desviaciones,
                            b.puntos_posibles
					
				    from (          
                                    select      sum(case  when opn.on_opcion in('DESVIACION','EMPTY') then p.p_pp else 0 end) as pdesviaciones, 
                                                sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos,
                                                sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones,
                                                sum(p.p_pp) as puntos_posibles,
                                                (	select sum(x.puntos_posibles) as pcriticos 
                                                        from (  select      sc.s_subcategoria,
                                                                            sum(p.p_pp) as puntos_posibles
                                                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                                                where opn.on_opcion!='NO APLICA' 
                                                                and ta.ta_tipo_aspi_id=?    
                                                                and cu.c_cuestionario_id=?    
                                                                and c.c_categoria=?

                                                                group by  sc.s_subcategoria
                                                                having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
                                                        ) as x  ) as pcriticos,
                                                        (	select 	sum(hk.frederick) as c
								from (    
									select      sc.s_subcategoria,
                                                                                    sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as frederick
									FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
									join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
									join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
									join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
									join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
									join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

									where opn.on_opcion!='NO APLICA' 
									and ta.ta_tipo_aspi_id=?    
									and cu.c_cuestionario_id=?    
									and c.c_categoria=?

									group by  sc.s_subcategoria
									having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0
							) as hk  ) as comp_desviacion

                                    FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)    
                                    join categoria c on(c.c_categoria_id=sc.c_categoria_id)    
                                    join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)  
                                    join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)  
                                    join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)  
                                    join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id) 

                                    where opn.on_opcion!='NO APLICA' 
                                    and ta.ta_tipo_aspi_id=?    
                                    and cu.c_cuestionario_id=?    
                                    and c.c_categoria=?

                            ) as b  ";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria));
        $out = $resultado->row();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * Retorna el resultado parcial de un subloque restando la constante de penalizacion cuando<br>
     * existe la constante de control
     * @param int $ta_tipo_aspi_id
     * @param int $c_cuestionario_id
     * @param char $c_categoria
     * @param char $s_subcategoria
     * @return objeto
     * *
     */
    public function parcal_xsubctrlcal_nn($ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria, $s_subcategoria, $s_subcategoria_id) {
        $query = "  
                select  if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,(b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0))) as puntos_obtenidos,     
        if(( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )<0 ,0,round((( b.puntos_posibles + ifnull(b.comp_desviacion,0) - b.pdesviaciones - ifnull(b.pcriticos,0) )/b.puntos_posibles)*100)) as calific,    
        b.criticos,  
        b.desviaciones, 
        b.puntos_posibles 
                     
        from (           
                        select      sum(case  when opn.on_opcion in('DESVIACION','EMPTY') then p.p_pp else 0 end) as pdesviaciones,  
                                    sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos, 
                                    sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones, 
                                    sum(p.p_pp) as puntos_posibles, 
                                    (       select sum(x.puntos_posibles) as pcriticos  
                                            from (  select      sc.s_subcategoria, 
                                                                sum(p.p_pp) as puntos_posibles 
                                                    FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                                                    join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                                                    join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                                                    join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                                                    join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                                                    join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)  

                                                    where opn.on_opcion!='NO APLICA'  
                                                    and ta.ta_tipo_aspi_id=?     
                                                    and cu.c_cuestionario_id=?     
                                                    and c.c_categoria=?
                                                    and sc.s_subcategoria=? 

                                                    group by  sc.s_subcategoria 
                                                    having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0 
                                            ) as x  ) as pcriticos, 
                                    (       select     sum(hk.frederick) as c 
                                            from (  select      sc.s_subcategoria, 
                                                                sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as frederick 
                                                    FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                                                    join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                                                    join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                                                    join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                                                    join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                                                    join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)  

                                                    where opn.on_opcion!='NO APLICA'  
                                                    and ta.ta_tipo_aspi_id=?     
                                                    and cu.c_cuestionario_id=?     
                                                    and c.c_categoria=?
                                                    and sc.s_subcategoria=? 

                                                    group by  sc.s_subcategoria 
                                                    having sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) > 0 
                                                ) as hk  
                                    ) as comp_desviacion 

                        FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                        join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                        join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                        join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                        join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                        join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)  

                        where opn.on_opcion!='NO APLICA'  
                        and ta.ta_tipo_aspi_id=?     
                        and cu.c_cuestionario_id=?     
                        and c.c_categoria=?
                        and sc.s_subcategoria=? 

                ) as b ";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria, $s_subcategoria,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria, $s_subcategoria,
                                                    $ta_tipo_aspi_id, $c_cuestionario_id, $c_categoria, $s_subcategoria));
        $out = $resultado->row();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * retorna una lista de las subcategoria de cada categoria
     * @param int $ta_tipo_aspi_id tipo de aspi 1 domicilio,2 mostrador,3 salon
     * @param char $c_categoria categoria A|S|P|I 
     * @return objects array
     * *
     */
    public function list_subcats($ta_tipo_aspi_id, $c_categoria) {
        $query = "  select sc.s_subcategoria_id,sc.s_subcategoria,sc.s_nombre
                        from tipo_aspi ta join categoria c on(ta.ta_tipo_aspi_id=c.ta_tipo_aspi_id)
                        join subcategoria sc on(c.c_categoria_id=sc.c_categoria_id)
                        where ta.ta_tipo_aspi_id=?
                        and c.c_categoria=?";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id, $c_categoria));
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * 
     * @param type $captcha_time
     * @param type $ip_address
     * @param type $word
     */
    function insertCaptcha($captcha_time, $ip_address, $word) {
        $query = "insert into captcha(captcha_id,captcha_time,ip_address,word)
                values(NULL,?,?,?)";
        $this->db->query($query, array($captcha_time, $ip_address, $word));
    }

    /**
     * 
     * @param type $ip_address
     */
    function deleteCaptcha($ip_address) {
        $this->db->query("delete from captcha where ip_address = '$ip_address'");
    }

    /**
     * 
     * @param type $binds
     * @return boolean
     */
    function captchaExist($binds) {
        $sql = "select count(*) as count from captcha where word = ? and ip_address = ? and captcha_time > ?";
        $query = $this->db->query($sql, $binds);
        $row = $query->row();

        if ($row->count == 0)
            return FALSE;
        else
            return TRUE;
    }

    /**
     * 
     * @return type
     * *
     */
    function listaOpcionesNa() {
        $query = "SELECT on_opcion_na_id,on_opcion FROM opcion_na where on_opcion != 'EMPTY' or ''";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    function updateCalifASPI($on_opcion_na_id, $c_calificacion_id) {
        $query = "update calificacion set on_opcion_na_id = ? where c_calificacion_id = ?";
        $this->db->query($query, array($on_opcion_na_id, $c_calificacion_id));
    }

    /**
     * Funcion que se encarga de traer el registro que coincida
     * con el numero de cuestionario y numero de estandar
     * @param int $c_cuestionario_id
     * @param String $pa_no_estandar
     * @return bool
     */
    public function existeRegistroPA($c_cuestionario_id, $pa_no_estandar) {
        $query = "SELECT count(*) dato from plan_accion where c_cuestionario_id = ? and pa_no_estandar = ?";
        $resultado = $this->db->query($query, array($c_cuestionario_id, $pa_no_estandar));
        $dato = $resultado->row();
        if ($dato->dato == 0) {
            return FALSE; //No existe
        } else {
            return TRUE; //Existe
        }
    }

    /**
     * Funcion que se encarga de borrar un registro en la tabla Plan de Acción
     * @param int $c_cuestionario_id
     * @param int $pa_no_estandar
     */
    public function borrarRegistroPA($c_cuestionario_id, $pa_no_estandar) {
        $query = "DELETE FROM plan_accion WHERE "
                . "c_cuestionario_id = ? and "
                . "pa_no_estandar = ? ";
        $this->db->query($query, array($c_cuestionario_id, $pa_no_estandar));
    }

    /**
     * Funcion que se encarga de guardar un registro en la tabla 
     * Plan de Acción
     * @param type $c_cuestionario_id
     * @param type $pa_no_estandar
     * @param type $on_opcion_na_id
     */
    public function guardarRegistroPA($c_cuestionario_id, $pa_no_estandar) {
        $query = "INSERT INTO plan_accion ("
                . "pa_no_estandar,"
                . "pa_descripcion_desviacion,"
                . "pa_accion_correctiva,"
                . "pa_fecha_cumplimiento,"
                . "pa_responsable,"
                . "c_cuestionario_id) values "
                . "(?,'','',now(),'',?)";
        $this->db->query($query, array($pa_no_estandar, $c_cuestionario_id));
    }

    /**
     * Funcion que regresa la tupla correspondiente a su id
     * @param int $on_opcion_na_id
     * @return object
     */
    public function getOpcionNA($on_opcion_na_id) {
        $query = "select on_opcion FROM opcion_na WHERE on_opcion_na_id = $on_opcion_na_id";
        $resultado = $this->db->query($query);
        $out = $resultado->row();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * 
     * @param type $c_cuestionario_id
     * @return type
     * *
     */
    public function getPlanAccion($c_cuestionario_id) {
        $query = "select pa.pa_no_estandar,pa.pa_descripcion_desviacion,pa_plan_accion_id,
                    pa.pa_accion_correctiva,pa.pa_fecha_cumplimiento,
                    pa.pa_responsable
                    FROM
                    plan_accion pa WHERE pa.c_cuestionario_id = $c_cuestionario_id
                    order by  SUBSTRING(pa_no_estandar,1,1), SUBSTRING(pa_no_estandar,3)*1 asc,pa.pa_no_estandar ";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * 
     * @param type $pa_descripcion_desviacion
     * @param type $pa_accion_correctiva
     * @param type $pa_fecha_cumplimiento
     * @param type $pa_responsable
     * @param type $pa_plan_accion_id
     */
    public function setPlanAccion($pa_descripcion_desviacion, $pa_accion_correctiva, $pa_fecha_cumplimiento, $pa_responsable, $pa_plan_accion_id) {

        
        $pa_descripcion_desviacion = ($pa_descripcion_desviacion!='')?"'".$pa_descripcion_desviacion."'":"' '";
        $pa_accion_correctiva = ($pa_accion_correctiva!='')?"'".$pa_accion_correctiva."'":"' '";
        $pa_fecha_cumplimiento = ($pa_fecha_cumplimiento!='')? "'".$pa_fecha_cumplimiento."'":"' '";
        $pa_responsable = ($pa_responsable!='')?"'".$pa_responsable."'":"' '";
        
        $query = "update plan_accion set    pa_descripcion_desviacion= $pa_descripcion_desviacion,
                                            pa_accion_correctiva=$pa_accion_correctiva,
                                            pa_fecha_cumplimiento=$pa_fecha_cumplimiento,
                                            pa_responsable=$pa_responsable
                   where pa_plan_accion_id = $pa_plan_accion_id ";
        $this->db->query($query);
    }

    /**
     * 
     * @param type $c_cuestionario_id
     * @return type
     */
    public function getDatosCuestionario($c_cuestionario_id) {
        $query = "select c.c_hora_inicio,c_fecha,c.c_comensales,c.c_aforo,c.c_fecha_usuario  "
                . "FROM cuestionario c WHERE c.c_cuestionario_id = $c_cuestionario_id";
        $resultado = $this->db->query($query);
        $out = $resultado->row();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    public function lista_cuestionarios() {
        $query = "  select c.c_cuestionario_id,
            c_fecha_usuario,
            c_hora_inicio,
            c_aforo,
            c_comensales,
            s.s_descripcion,
            s.s_clave,ta.ta_tipo_aspi_id,ta_tipo
                    from cuestionario c 
                    join sucursal s on(c.s_sucursal_id=s.s_sucursal_id)
                    join calificacion ca on(ca.c_cuestionario_id=c.c_cuestionario_id)
                    join pregunta p on(p.p_pregunta_id=ca.p_pregunta_id)
                    join subcategoria sc on(sc.s_subcategoria_id=p.s_subcategoria_id)
                    join categoria cat on(cat.c_categoria_id=sc.c_categoria_id)
                    join tipo_aspi ta on(ta.ta_tipo_aspi_id=cat.ta_tipo_aspi_id)
                    group by  c.c_cuestionario_id";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }
    /**
     * 
     * @return type
     * Atributos del Objeto Rsultante
     *  nAuditorias
     *  sucursal
     *  auditor
     *  clave de sucursal
     *  s_sucursal_id
     */
    public function getListaSucAudit(){
        $query = "select count(s.s_sucursal_id) 'nAuditorias',
                                    s.s_descripcion sucursal,
                                    s.s_clave,
                            concat(a.a_nombre,' ',a.a_apellido) auditor,
                            s.s_sucursal_id claveSuc
                    from  cuestionario c
                    join sucursal s using (s_sucursal_id)
                    join administrativo a on a.a_administrativo_id = s.a_admin_serv_id 
                    group by s.s_sucursal_id

                    order by nAuditorias desc,
                                    sucursal";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }
    /**
     * 
     * @return type
     * Atributos del Objeto Resultante
     *  sucursal
     *  estado
     *  s_sucursal_id
     *  clave de sucursal
     *  quien debe auditar
     */
    public function getListaSucNoAudit(){
        $query = "select 
                    s.s_descripcion sucursal,
                            'SIN AUDITAR' estado,
                        s.s_sucursal_id claveSuc,
                        s.s_clave,
                        concat(a.a_nombre,' ',a.a_apellido) auditor
                    from cuestionario c
                    right join sucursal s on s.s_sucursal_id = c.s_sucursal_id
                    join administrativo a on a.a_administrativo_id = s.a_admin_serv_id
                    where c.s_sucursal_id IS NULL";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }
}
